-- EXEC SP_SEARCH_BACKLOG_ORDERS_FOR_RELEASE -1, null, 0

Alter PROC SP_SEARCH_BACKLOG_ORDERS_FOR_RELEASE( @processStep_Id INT, @salesOrder_No VARCHAR(50), @customer_Id INT )
AS
BEGIN
	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)

	SET @SQLString = N'SELECT SO.SalesOrder_Id, SO.SalesOrder_No, SO.SO_Customer_PO_Number, SO_IS_Sample, COOL.LineItem_Id, COOL.COOL_Line_Item_No, P.Part_Number, C.C_Name, COOL.COOL_Qty, CONVERT( VARCHAR, COOL.COOL_Requested_date, 101 ) COOL_Requested_date, CONVERT( VARCHAR, COOL.COOL_Schedule_Date, 101 ) COOL_Schedule_Date, COOL_Shipment_No FROM Sales_Order SO, Customer_open_Order_LineItem COOL, Product P, Customer C WHERE SO.SalesOrder_Id = COOL.SalesOrder_ID AND COOL.Product_id = P.Product_Id AND SO.Customer_ID = C.Customer_ID AND ( ( SO.SO_Status = ''APPROVED'' ) OR ( SO.SO_Status = ''HOLD'') ) AND ( ( COOL.Cool_Status = ''APPROVED'' ) OR ( COOL.Cool_Status = ''HOLD'' ) ) AND COOL.LineItem_Id NOT IN ( SELECT LineItem_Id from Program_Release where Status <> ''CANCELLED'' ) '
	SET @ParamDefinition = N'@Prm_ProcessStep_ID VARCHAR(10), @Prm_Customer_ID VARCHAR(10), @Prm_Sales_Order_No varchar(50)'

	IF @processStep_Id <> -1
	BEGIN
		SET @SQLString = @SQLString + ' AND P.ProcessStep_id = @Prm_ProcessStep_ID'
	END

	IF @customer_Id <> -1 AND @customer_Id <> 0 
	BEGIN
		SET @SQLString = @SQLString + ' AND C.Customer_id = @Prm_Customer_ID'
	END

	IF @salesOrder_No IS NOT NULL AND LEN( ltrim( rtrim( @salesOrder_No ) ) ) > 0
	BEGIN
		SET @SQLString = @SQLString + ' AND SO.SalesOrder_No = @Prm_Sales_Order_No'
	END

	--SET @SQLString = @SQLString + ' ORDER BY SO.SalesOrder_No'
	SET @SQLString = @SQLString + ' ORDER BY CONVERT( DATETIME, COOL.COOL_Schedule_Date ) '


--	select * from Program_Release

	EXECUTE SP_EXECUTESQL @SQLString, @ParamDefinition, @Prm_ProcessStep_ID = @processStep_Id, @Prm_Customer_ID = @customer_id, @Prm_Sales_Order_No = @salesOrder_No

END